package com.pic.mgt.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Collections;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Vector;

import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;

import com.pic.mgt.utils.IgxCollectionUtils;
import com.pic.mgt.utils.SqlScriptUtils;


public abstract class BusinessTargetActionDao extends BasicDao implements
		IBusinessTargetActionDao {
	
	private final String updateLastTxTimeByFilenameSqlScript;
	private final String updateCreatingTimeByFilenameSqlScript;
	private final String findBasicBusinessTargetActionInfoByUnitNameSqlScript;
	private final String findBasicBusinessTargetActionInfoByFileNameSqlScript;
	private final String findBasicBusinessTargetActionInfoListByUnitNameWithOrderByCreatingTimeDESCSqlScript;
	private final String findBasicBusinessTargetActionInfoListByUnitNameAndTerminalIdSqlScript;
	
	public BusinessTargetActionDao() {
		super();
		
		// 建立 update last transaction time by target file 的 Sql Script
		this.updateLastTxTimeByFilenameSqlScript = createUpdateLastTxTimeByFilenameSqlScript();
		
		// 建立 update creating time by file name 的 Sql Script
		this.updateCreatingTimeByFilenameSqlScript = createUpdateCreatingTimeByFilenameSqlScript();
		
		// 建立 find basic business target action info by unit name 的 sql script
		this.findBasicBusinessTargetActionInfoByUnitNameSqlScript = createFindBasicBusinessTargetActionInfoByUnitNameSqlScript();
		
		// 建立 find basic business target action info by file name 的 sql script
		this.findBasicBusinessTargetActionInfoByFileNameSqlScript = createFindBasicBusinessTargetActionInfoByFileNameSqlScript();
		
		// 建立 find basic business target action info list by unit name with order by creating time desc 的 sql script
		this.findBasicBusinessTargetActionInfoListByUnitNameWithOrderByCreatingTimeDESCSqlScript =  createFindBasicBusinessTargetActionInfoListByUnitNameWithOrderByCreatingTimeDESCSqlScript();
		
		// 建立 find basic business target action info list by unit name and terminal id 的 sql script
		this.findBasicBusinessTargetActionInfoListByUnitNameAndTerminalIdSqlScript = createFindBasicBusinessTargetActionInfoListByUnitNameAndTerminalIdSqlScript();
		
	}
	
	private String createFindBasicBusinessTargetActionInfoByFileNameSqlScript() {
		Vector<String> selectFieldsVector =
			new Vector<String>();

		selectFieldsVector.add(_terminalId);
		selectFieldsVector.add(_fileName);
		selectFieldsVector.add(_fileSize);
		selectFieldsVector.add(_lastTxTime);
		selectFieldsVector.add(_creatingTime);

		return SqlScriptUtils.createNamedParamQueryScriptWithSingleCondition(
				getTableName(), selectFieldsVector, _fileName);
	}
	
	private String createFindBasicBusinessTargetActionInfoListByUnitNameAndTerminalIdSqlScript() {
		Vector<String> selectFieldsVector =
			new Vector<String>();

		selectFieldsVector.add(_terminalId);
		selectFieldsVector.add(_fileName);
		selectFieldsVector.add(_fileSize);
		selectFieldsVector.add(_lastTxTime);
		selectFieldsVector.add(_creatingTime);

		return SqlScriptUtils.createNamedParamQueryScriptWithMultiConditions(
				getTableName(),
				selectFieldsVector,
				new String[] {_unitName, "AND", _terminalId});
	}
	
	private String createFindBasicBusinessTargetActionInfoListByUnitNameWithOrderByCreatingTimeDESCSqlScript() {
		Vector<String> selectFieldsVector =
			new Vector<String>();

		selectFieldsVector.add(_terminalId);
		selectFieldsVector.add(_fileName);
		selectFieldsVector.add(_fileSize);
		selectFieldsVector.add(_lastTxTime);
		selectFieldsVector.add(_creatingTime);

		return SqlScriptUtils.createNamedParamQueryScriptWithSorting(
				getTableName(),
				selectFieldsVector,
				_unitName,
				new String[] {_creatingTime, "DESC"});
	}
	
	private String createFindBasicBusinessTargetActionInfoByUnitNameSqlScript() {
		Vector<String> selectFieldsVector =
			new Vector<String>();

		selectFieldsVector.add(_terminalId);
		selectFieldsVector.add(_fileName);
		selectFieldsVector.add(_fileSize);
		selectFieldsVector.add(_lastTxTime);
		selectFieldsVector.add(_creatingTime);

		return SqlScriptUtils.createNamedParamQueryScriptWithSingleCondition(
				getTableName(), selectFieldsVector, _unitName);
	}
	
	private String createUpdateCreatingTimeByFilenameSqlScript() {
		Vector<String> updateFieldsVector = new Vector<String>();
		
		updateFieldsVector.add(_creatingTime);
		
		return SqlScriptUtils.createNamedParamUpdateScript(
										getTableName(),
										updateFieldsVector,
										_fileName);
	}
	
	private String createUpdateLastTxTimeByFilenameSqlScript() {
		// 要更新的欄位
		Vector<String> fieldsVector = new Vector<String>();
		fieldsVector.add(_lastTxTime);
		
		return
			SqlScriptUtils.createNamedParamUpdateScript(getTableName(),
														fieldsVector,
														_fileName);
	}

	@Override
	public int updateLastTxTimeByFilename(Timestamp lastTxTime,
			String filename) throws DataAccessException {
		if (null != lastTxTime && null != filename) {
			Map<String, Object> updateValuesMap =
				IgxCollectionUtils.convertToMap(
						new Object[] {_lastTxTime, lastTxTime,
									  "__ConditionValue", filename});
		
		return
			super.namedParameterJdbcTemplate.update(
							this.updateLastTxTimeByFilenameSqlScript,
							updateValuesMap);
		}
		else {
			return 0;
		}
	}
	
	@Override
	public int updateCreatingTimeByFilename(Timestamp creatTime, String filename)
			throws DataAccessException {
		if (null != creatTime && null != filename) {
			Map<String, Object> updateValuesMap =
				IgxCollectionUtils.convertToMap(
						new Object[] {_creatingTime, creatTime,
									  "__ConditionValue", filename});
		
		return
			super.namedParameterJdbcTemplate.update(
							this.updateCreatingTimeByFilenameSqlScript,
							updateValuesMap);
		}
		else {
			return 0;
		}
	}
	
	@Override
	public BasicBusinessTargetActionInfoBean findBasicBusinessTargetActionInfoByFileName(
			String filename) throws DataAccessException {
		try {
			return
			super.namedParameterJdbcTemplate.queryForObject(
					this.findBasicBusinessTargetActionInfoByFileNameSqlScript,
					Collections.singletonMap((String)_fileName, (Object)filename),
					new RowMapper<BasicBusinessTargetActionInfoBean>() {
						public BasicBusinessTargetActionInfoBean mapRow(ResultSet rs, int rowNum) throws SQLException {
							BasicBusinessTargetActionInfoBean basicBusinessTargetActionInfoBean = new BasicBusinessTargetActionInfoBean();
						
							basicBusinessTargetActionInfoBean.setTerminalId(rs.getString(_terminalId));
							basicBusinessTargetActionInfoBean.setFilename(rs.getString(_fileName));
							basicBusinessTargetActionInfoBean.setFilesize(rs.getLong(_fileSize));
							basicBusinessTargetActionInfoBean.setCreatingTime(new Date(rs.getTimestamp(_creatingTime).getTime()));
							
							Timestamp latestTxTimestamp = rs.getTimestamp(_lastTxTime);
							if (null != latestTxTimestamp) {
								basicBusinessTargetActionInfoBean.setLastTransactionTime(new Date(latestTxTimestamp.getTime()));
							}
							else {
								basicBusinessTargetActionInfoBean.setLastTransactionTime(null);
							}
							
							return basicBusinessTargetActionInfoBean;
						}
					}
				);
		} catch (EmptyResultDataAccessException e) {
			return null;
		} catch (DataAccessException e) {
			throw e;
		}
	}
	
	@Override
	public BasicBusinessTargetActionInfoBean findBasicBusinessTargetActionInfoByUnitName(
			String unitName) throws DataAccessException {
		try {
			return
			super.namedParameterJdbcTemplate.queryForObject(
					this.findBasicBusinessTargetActionInfoByUnitNameSqlScript,
					Collections.singletonMap((String)_unitName, (Object)unitName),
					new RowMapper<BasicBusinessTargetActionInfoBean>() {
						public BasicBusinessTargetActionInfoBean mapRow(ResultSet rs, int rowNum) throws SQLException {
							BasicBusinessTargetActionInfoBean basicBusinessTargetActionInfoBean = new BasicBusinessTargetActionInfoBean();
						
							basicBusinessTargetActionInfoBean.setTerminalId(rs.getString(_terminalId));
							basicBusinessTargetActionInfoBean.setFilename(rs.getString(_fileName));
							basicBusinessTargetActionInfoBean.setFilesize(rs.getLong(_fileSize));
							basicBusinessTargetActionInfoBean.setCreatingTime(new Date(rs.getTimestamp(_creatingTime).getTime()));
							
							Timestamp latestTxTimestamp = rs.getTimestamp(_lastTxTime);
							if (null != latestTxTimestamp) {
								basicBusinessTargetActionInfoBean.setLastTransactionTime(new Date(latestTxTimestamp.getTime()));
							}
							else {
								basicBusinessTargetActionInfoBean.setLastTransactionTime(null);
							}
							
							return basicBusinessTargetActionInfoBean;
						}
					}
				);
		} catch (EmptyResultDataAccessException e) {
			return null;
		} catch (DataAccessException e) {
			throw e;
		}
	}
	
	@Override
	public List<BasicBusinessTargetActionInfoBean> findBasicBusinessTargetActionInfoListByUnitNameWithOrderByCreatingTimeDESC(
			String unitName) throws DataAccessException {
		try {
			return
				super.namedParameterJdbcTemplate.query(
						this.findBasicBusinessTargetActionInfoListByUnitNameWithOrderByCreatingTimeDESCSqlScript,
						IgxCollectionUtils.convertToMap(
								new Object[] {_unitName, unitName}),
					new RowMapper<BasicBusinessTargetActionInfoBean>() {
						public BasicBusinessTargetActionInfoBean mapRow(ResultSet rs, int rowNum) throws SQLException {
							BasicBusinessTargetActionInfoBean basicBusinessTargetActionInfoBean = new BasicBusinessTargetActionInfoBean();						
							
							basicBusinessTargetActionInfoBean.setTerminalId(rs.getString(_terminalId));
							basicBusinessTargetActionInfoBean.setFilename(rs.getString(_fileName));
							basicBusinessTargetActionInfoBean.setFilesize(rs.getLong(_fileSize));
							basicBusinessTargetActionInfoBean.setCreatingTime(new Date(rs.getTimestamp(_creatingTime).getTime()));
							
							Timestamp latestTxTimestamp = rs.getTimestamp(_lastTxTime);
							if (null != latestTxTimestamp) {
								basicBusinessTargetActionInfoBean.setLastTransactionTime(new Date(latestTxTimestamp.getTime()));
							}
							else {
								basicBusinessTargetActionInfoBean.setLastTransactionTime(null);
							}
							
							return basicBusinessTargetActionInfoBean;
						}
					  });
		} catch (EmptyResultDataAccessException e) {
			return null;
		} catch (DataAccessException e) {
			throw e;
		}
	}
	
	@Override
	public List<BasicBusinessTargetActionInfoBean> findBasicBusinessTargetActionInfoListByUnitNameAndTerminalId(
			String unitName, String terminalId) throws DataAccessException {
		try {
			return
				super.namedParameterJdbcTemplate.query(
						this.findBasicBusinessTargetActionInfoListByUnitNameAndTerminalIdSqlScript,
						IgxCollectionUtils.convertToMap(
								new Object[] {_unitName, unitName,
											  _terminalId, terminalId}),
					new RowMapper<BasicBusinessTargetActionInfoBean>() {
						public BasicBusinessTargetActionInfoBean mapRow(ResultSet rs, int rowNum) throws SQLException {
							BasicBusinessTargetActionInfoBean basicBusinessTargetActionInfoBean = new BasicBusinessTargetActionInfoBean();						
							
							basicBusinessTargetActionInfoBean.setTerminalId(rs.getString(_terminalId));
							basicBusinessTargetActionInfoBean.setFilename(rs.getString(_fileName));
							basicBusinessTargetActionInfoBean.setFilesize(rs.getLong(_fileSize));
							basicBusinessTargetActionInfoBean.setCreatingTime(new Date(rs.getTimestamp(_creatingTime).getTime()));
							
							Timestamp latestTxTimestamp = rs.getTimestamp(_lastTxTime);
							if (null != latestTxTimestamp) {
								basicBusinessTargetActionInfoBean.setLastTransactionTime(new Date(latestTxTimestamp.getTime()));
							}
							else {
								basicBusinessTargetActionInfoBean.setLastTransactionTime(null);
							}
							
							return basicBusinessTargetActionInfoBean;
						}
					  });
		} catch (EmptyResultDataAccessException e) {
			return null;
		} catch (DataAccessException e) {
			throw e;
		}
	}
}
